Create the object for selecting Employee Name

A multi-column list box will be used to display a list of employee names. The person executing the report will select desired names to include in the query. Both employee last name and first name will be contained in the list box.

  1. Begin by clicking the multi-column list box icon Multi-column listbox icon then click anywhere in the design area to place the object.
  2. In the properties area for this object, change the Multi Select property from no to yes. This will allow the user to select more than one entry.
  3. Since this object is used later when building an SQL query, give the object a meaningful name such as “EmployeeList” by editing the Object Properties area.
  4. Double-click on the new object to bring up the following dialog box that allows you to specify which employees are to be included in the query.
  5. Choose SQL statement to obtain names from the database.
    The Choice Entry dialog box used to specify if manual entries or SQL statements are to be used to populate an object.

Object Choices Property: Drop-down boxes, list boxes, and multi-column list boxes contain the “choices” property which allows these objects to accept input from the user and display query results as well.

  1. After selecting “SQL statement”, the following dialog box appears:
    The choice entry dialog box for entering an S Q L statement.
  2. Click the Build Query icon build query icon which brings up the Build Query dialog box. Visit this page for more information on the Build Query dialog box.
    The Build Query dialog box before any information is entered.
  3. Click Show Tables at the upper left which displays the list of tables within the database. 
  4. Double-click the "Employees" table to add it to the query. The Employees table contains the employee names.
  5. Double-click "last_name" and "first_name" to move them to the query table at the bottom of the screen. Fields that appear in the query on the Visible Fields tab will be included in the SELECT statement.
    The Build Query dialog box, which now contains the Employees table. The last name and first name database fields are chosen to be included in the DataBlock and are displayed.
  6. We only want to include the employees in the Sales department, so we need to add a condition (WHERE clause) to the SQL query. To add a WHERE clause to obtain only the employees in the Sales Department, click the “Conditional Fields (WHERE)” tab.
    The conditional fields (where) tab. The fields include and/or, table, field, and condition. They are currently blank.
  7. Double-click on dept_id to autofill the and/or, Table, and Field fields.
    The department ID filled in for the WHERE statement.
  8. Click the “Condition” field, then click the ellipsis button ellipses button which brings up the SQL Editor. Enter ='dep03' in the text edit box.
  9. Click OK to continue. The completed condition is shown in the figure below.
    This image shows dep03 added as a condition in the WHERE statement.
  10. Click the View SQL button at the top of the Build Query dialog box which shows the resulting SQL Query translated by the Visual Design. It shows that only employees within ‘dep03’ will be listed in the multi-column list box.
    The view s q l window with the completed query.
  11. Click Next. The next screen shows a preview of the listbox.
  12. Click Finish.
  13. To check your work, click the Save button at the top left of the Build Query dialog box to save your work, then click the Test icon Test icon to test the form and query design. Note that you can specify how many results you want to display for the test at the bottom of the form. Enter 0 to display all results.
    This shows the datablock dashboard that's been created. The image that was inserted is in the upper left, the date fields are in the upper right, and the multi column listbox with names pulled from the database.
  14. Note that the multi-column list box is now populated with the list of employees in the Sales Department. Click Close to continue the DataBlock design.

This completes the activities required to create the form that is executed when running the report. The next section in the guide describes how to create the query that will obtain and display information from the database onto the form.